Oracle имеет два собственных типа переменных. Переменные связи (bind variables) предназначены для хранения отдельных значений присваиваемых при исполнении команды.
Переменные подстановки (Substitution variables) позволяют хранить вводимые вручную перед исполнением команды значения.
Переменные подстановки обозначаются префиксом &. Распознав такую переменную в процессе исполнения Oracle просит ввести ее значение и после ввода продолжает исполнение запроса пользователя.
Если символьная переменная в тексте программы записана в одинарных кавычках (например, ‘YEAR_SAL’), при вводе кавычки не ставятся.
Переменная подстановки с двумя амперсендами (например, &&NUM1) запрашивается один раз за сеанс.
Поскольку значения переменных подстановки запрашиваются перед исполнением запроса, то с их помощью может вводиться и часть текста команды.
Например, команда SELECT &STOLBETS ... позволяет при каждом исполнении менять имя выводимого столбца.
Команда SQL*Plus DEF[INE] позволяет задать значение переменной на сеанс или до выполнения команды UNDEF[INE] отменяющей определение.
Пример:
DEFINE REM=SAL*12
SELECT ENAME, JOB, REM FROM EMP;
Замечание 1: Команда DEF выдает все определенные переменные, команда DEF <имя> выдает значение указанной переменной, если же она не определена, выдается undefinite.
Команда ACCEPT позволяет определять переменные и присваивать им значения в интерактивном режиме.
Формат команды:
ACC[EPT] имя_переменной [NUMBER|CHAR] [PROMPT|NOPROMPT ‘текст_подсказки’] [HIDE]
где,
NUMBER|CHAR -- тип переменной;
PROMPT -- высвечивание подсказки;
HIDE -- скрывает вводимый текст; удобен при вводе паролей.
Для командных файлов, содержащих переменные подстановки используют переменные специального вида. Их девять, имена от 1 до 9. Команда START в этом случае используется в формате:
START список_фактических_параметров
причем первый фактический параметр заменяет &1, второй &2 и т.д.
1. Напишем команду, использующую приглашение во время выполнения. Назначение -- перечислить всех сотрудников. принятых на работу в период между двумя произвольными датами.
SELЕСТ ENAME, JOB, MGR, DEPTNO FRОМ EMP WHERE JOB = '&JOB';
В появившемся запросе "Enter value for job:" введите MANAGER и в окне SQL*Plus Вы должны получить следующие значения:
ENAME JOB MGR DEPTNO ---------- --------- ---------- ---------- JONES MANAGER 7839 20 BLAKE MANAGER 7839 30 CLARK MANAGER 7839 10
2. Определим переменную, представляюшую выражение для вычисления полных годовых начислений сотрудникам. Используем эту переменную в команде, которая находит всех сотрудников, чьи годовые начисления не меньше $30000.
DEFINE RE = 'SAL*12+NVL(COMM,0)';
SELЕСТ ENAME, &RE FRОМ EMP WHERE &RE > 30000;
В появившемся запросе "Enter value for job:" введите MANAGER и в окне SQL*Plus Вы должны получить следующие значения:
ENAME SAL*12+NVL(COMM,0) ---------- ------------------ JONES 35700 BLAKE 34200 SCOTT 36000 KING 60000 FORD 36000